/**
 * 
 */
package es.ats.codington.festival.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;


import es.ats.codington.festival.domain.People;
import es.ats.codington.festival.domain.Visitor;
import es.ats.codington.festival.exceptions.DAOException;
import es.ats.codington.festival.interfaces.daos.IVisitorDAO;
import es.ats.codington.festival.mappers.PeopleMapper;
import es.ats.codington.festival.mappers.VisitorMapper;




/**
 * @author Codington_RED
 *
 */ 
public class VisitorDAOImpl implements IVisitorDAO{
	
	private JdbcTemplate jdbcTemplateObject;
	private Properties queryProperties;
	
	
	public int key;

	
	/**
	 * Default constructor for VisitorDAOImpl
	 * @param conn
	 */
	public VisitorDAOImpl(JdbcTemplate jdbcTemplateObject,Properties queryProperties){
		this.jdbcTemplateObject = jdbcTemplateObject;
		this.queryProperties = queryProperties;
	}
	/**
	 * obtain the personal ID from People database table.
	 * @param p
	 * @return idPeople
	 */
	@Override
	public int getIdPeople(final People people) {
		
		int idPeople = 0;
		
		List<People> peoples = jdbcTemplateObject.query(queryProperties.getProperty("idPeople"), 
				new PreparedStatementSetter(){

					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						ps.setString(1, people.getDni());
						
					}
			
		}, new PeopleMapper());
		
		
		idPeople = peoples.get(0).getIdPeople();
		
		return idPeople;
		
		
	}
	
	
	/**
	 * obtain the personal ID from Visitor database table. It should be the same as people table.
	 * @param v
	 * @return idVisitor
	 */
	
	@Override
	public int getIdVisitor(final Visitor visitor) {
		
		int idVisitor = 0;
		
		List<Visitor> visitors = jdbcTemplateObject.query(queryProperties.getProperty("idVisitor"), 
				new PreparedStatementSetter(){

					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						ps.setString(1, visitor.getUserName());
						
					}
			
		}, new VisitorMapper());
		
		idVisitor = visitors.get(0).getVisitorId();
		
		return idVisitor;
		
	}
	
	/**
	 * Obtains a list with the visitors registered in 
	 * @param resultSet
	 * @return visitors
	 * @throws SQLException
	 */
	private List<Visitor> getVisitorList(ResultSet resultSet) throws SQLException {
			
		 List<Visitor> visitors = new ArrayList<Visitor>();
			
		 while(resultSet.next()) {	      
			 Visitor visitor = new Visitor();
			
			 
			 visitor.setVisitorId(resultSet.getInt("idVisitor"));
			 visitor.setUserName(resultSet.getString("userName"));
			 visitor.setPassword(resultSet.getString("password"));
			 visitor.setAdmin(resultSet.getBoolean("admin"));
			 
			 visitors.add(visitor); 
			    
		}
			
		return visitors;
    }
	
	/**
	 * obtains a list with the people.
	 * @param resultSet
	 * @return peoples
	 * @throws SQLException
	 */
	private List<People> getPeopleList(ResultSet resultSet) throws SQLException {
		
		 List<People> peoples = new ArrayList<People>();
			
		 while(resultSet.next()) {	      
			 People people = new People();
			
			 people.setIdPeople(resultSet.getInt("idPeople"));
			 people.setFirstName(resultSet.getString("firstName"));
			 people.setLastName(resultSet.getString("lastName"));
			 people.setDni(resultSet.getString("dni"));
			 people.setEmail(resultSet.getString("email"));
			 people.setPhoneNumber(resultSet.getString("phoneNumber"));
			 people.setAddress(resultSet.getString("address"));
			 
			 peoples.add(people);
			    
		}
			
		return peoples;
   }
	
	/**
	 * obtains a visitor.
	 * @param resultSet
	 * @return visitor
	 * @throws SQLException
	 */
	@Override
	public Visitor getVisitor(ResultSet resultSet) throws SQLException {
		
		List<Visitor> visitors = this.getVisitorList(resultSet);	
		Visitor visitor = null;
		
		if(!visitors.isEmpty()) {
		    visitor = visitors.get(0);
		    
		}
				
		return visitor;
	}
	
	/**
	 * Retrieves all the people registered in the system.
	 * @param resultSet
	 * @return people
	 * @throws SQLException
	 */
	@Override
	public People getPeople(ResultSet resultSet) throws SQLException {
		
		List<People> peoples = this.getPeopleList(resultSet);	
		People people = null;
		
		if(!peoples.isEmpty()) {
		    people = peoples.get(0);
		    
		}
				
		return people;
	}
	
	
	/**
	 * Confirms that login have been done right
	 * @param visitor
	 * @return isAdmin 
	 * @throws DAOException
	 * @throws SQLException
	 */
	@Override
	public boolean getCheckLogin(final Visitor visitor) throws DAOException, SQLException {
		
		boolean isLogin = false;
		
		List<Visitor> visitors = jdbcTemplateObject.query(queryProperties.getProperty("checkLogin"), 
				new PreparedStatementSetter(){

					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						ps.setString(1, visitor.getUserName());
						ps.setString(2, visitor.getPassword());
						
					}
			
		}, new VisitorMapper());
		
		if(visitors.size() == 1){
			
			isLogin = true;
		}
		
		return isLogin;
		
	}
	/**
	 * check if the visitor that has just entered to the system is an administrator
	 * @param visitor
	 * @return isLogin
	 * @throws DAOException
	 * @throws SQLException
	 */
	@Override
	public boolean getCheckAdmin(final Visitor visitor) throws DAOException, SQLException {
		
		
		
		List<Visitor> visitors = jdbcTemplateObject.query(queryProperties.getProperty("checkAdmin"), 
				new PreparedStatementSetter(){

					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						ps.setString(1, visitor.getUserName());
						ps.setString(2, visitor.getPassword());
						
					}
			
		}, new VisitorMapper());
		
		
		return visitors.get(0).isAdmin();
		
		
	}
	/**
	 * check if the people logged exists at the database table
	 * @param people
	 * @return isLogin
	 * @throws DAOException
	 * @throws SQLException
	 */
	@Override
	public boolean getCheckPeople(final People people) throws DAOException, SQLException {
		
		boolean isLogin = false;
		
		List<People> peoples = jdbcTemplateObject.query(queryProperties.getProperty("checkPeople"), 
				new PreparedStatementSetter(){

					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						ps.setString(1, people.getDni());
						
					}
			
		}, new PeopleMapper());
		
		if(peoples.size() == 1){
			
			isLogin = true;
		}
		
		
		return isLogin;
		
	}
	/**
	 * method that allows us to introduce people data into the System
	 * @param people
	 * @return insertPeople
	 * @throws DAOException
	 */
	@Override
	public boolean insertData(final People people) throws DAOException{
		
		boolean insertPeople = false;
		Integer affectedRows = 0;
		
		KeyHolder keyHolder = new GeneratedKeyHolder();
		affectedRows = jdbcTemplateObject.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps =
                        conn.prepareStatement(queryProperties.getProperty("insertPeople"), PreparedStatement.RETURN_GENERATED_KEYS);
						ps.setString(1, people.getFirstName());
						ps.setString(2, people.getLastName());
						ps.setString(3, people.getDni());
						ps.setString(4, people.getEmail());
						ps.setString(5, people.getPhoneNumber());
						ps.setString(6, people.getAddress());
						
						return ps;
			}
			
		}, keyHolder);
		
		key = keyHolder.getKey().intValue();
		people.setIdPeople(key);
		
		if (affectedRows > 0) {
			insertPeople = true;
		}
		
		return insertPeople;
		
		
		
	}
		

	/**
	 * method that allows us to introduce visitor information into the System
	 * @param visitor
	 * @return insertVisitor
	 * @throws DAOExcpetion
	 */
	@Override
	public boolean insertDataV(final Visitor visitor) throws DAOException {
		boolean insertVisitor = false;
		Integer affectedRows = 0;
		
		affectedRows = jdbcTemplateObject.update(queryProperties.getProperty("insertVisitor"), 
				new PreparedStatementSetter(){

					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						ps.setInt(1, visitor.getVisitorId());
						ps.setString(2, visitor.getUserName());
						ps.setString(3, visitor.getPassword());
						ps.setBoolean(4, visitor.isAdmin());
						
					}
		});
		
		if (affectedRows > 0) {
			insertVisitor = true;
		}
		
		return insertVisitor;
		
	}
	
	/**
	 * method that allows us to introduce people information into the System
	 * @param visitor
	 * @return insertVisitor
	 * @throws DAOException
	 */
	@Override
	public boolean insertData(final Visitor visitor) throws DAOException {
		
		
		boolean insertVisitor = false;
		Integer affectedRows = 0;
		
		affectedRows = jdbcTemplateObject.update(queryProperties.getProperty("insertVisitor"), 
				new PreparedStatementSetter(){

					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						ps.setInt(1, key);
						ps.setString(2, visitor.getUserName());
						ps.setString(3, visitor.getPassword());
						ps.setBoolean(4, visitor.isAdmin());
						
					}
		});
		
		visitor.setVisitorId(key);
		
		if (affectedRows > 0) {
			insertVisitor = true;
		}
		
		return insertVisitor;

	}

	/**
	 * look for a visitor into the database table.
	 * @param visitor
	 * @return v
	 * @throws DAOException
	 */
	@Override
	public Visitor searchVisitor(final Visitor visitor) throws DAOException {
		
		List<Visitor> visitors = jdbcTemplateObject.query(queryProperties.getProperty("searchVisitor"), 
				new PreparedStatementSetter(){

					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						  ps.setInt(1, visitor.getVisitorId());
						
					}
			
		}, new VisitorMapper());
		
		return visitors.get(0);
		
	}
	
	/**
	 * look for people existing inside of database table
	 * @param people
	 * @return p
	 * @throws DAOException
	 */
	@Override
	public People searchPeople(final People people) throws DAOException {
		
		List<People> peoples = jdbcTemplateObject.query(queryProperties.getProperty("searchPeople"), 
				new PreparedStatementSetter(){

					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						  ps.setInt(1, people.getIdPeople());
						
					}
			
		}, new PeopleMapper());
		
		return peoples.get(0);
		
	}
	
	/**
	 * update the data about DNI visitor
	 * @param people
	 * @return affectedRows
	 * @throws DAOException
	 */
	@Override
	public int updateVisitorDni(final People people) throws DAOException {
		
		Integer affectedRows = 0;
		
		affectedRows = jdbcTemplateObject.update(queryProperties.getProperty("updatePeopleDni"), 
				new PreparedStatementSetter(){

					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						ps.setString(1, people.getFirstName());
						ps.setString(2, people.getLastName());
						ps.setString(3, people.getDni());
						ps.setString(4, people.getEmail());
						ps.setString(5, people.getPhoneNumber());
						ps.setString(6, people.getAddress());
						ps.setString(7, people.getDni());
						
					}
		});
		
		return affectedRows;
		
		
	}
	/**
	 * update all the information relative to visitor database table
	 * @param people
	 * @return affectedRows
	 * @throws DAOException
	 */
	@Override
	public int updateVisitor(final People people) throws DAOException {
		
		Integer affectedRows = 0;
		
		affectedRows = jdbcTemplateObject.update(queryProperties.getProperty("updatePeople"), 
				new PreparedStatementSetter(){

					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						ps.setString(1, people.getFirstName());
						ps.setString(2, people.getLastName());
						ps.setString(3, people.getDni());
						ps.setString(4, people.getEmail());
						ps.setString(5, people.getPhoneNumber());
						ps.setString(6, people.getAddress());
						ps.setInt(7, people.getIdPeople());
						
					}
		});
		
		return affectedRows;
		
	}

	/**
	 * update all the information relative to visitor database table
	 * @param visitor
	 * @return affectedRows
	 * @throws DAOException
	 */
	@Override
	public int updateVisitor(final Visitor visitor) throws DAOException {
		
		Integer affectedRows = 0;
		
		affectedRows = jdbcTemplateObject.update(queryProperties.getProperty("updateVisitor"), 
				new PreparedStatementSetter(){

					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						ps.setString(1, visitor.getUserName());
						ps.setString(2, visitor.getPassword());
						ps.setBoolean(3, visitor.isAdmin());
						ps.setInt(4, visitor.getVisitorId());
						
					}
		});
		
		return affectedRows;
		
	}
	
	/**
	 * changes or updates the visitor password
	 * @param visitor
	 * @return affectedRows
	 * @throws DAOException
	 */
	@Override
	public int changePassword(final Visitor visitor) throws DAOException {
		
		Integer affectedRows = 0;
		
		affectedRows = jdbcTemplateObject.update(queryProperties.getProperty("changePasswordVisitor"), 
				new PreparedStatementSetter(){

					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						
						 ps.setString(1, visitor.getPassword());
						 ps.setString(2, visitor.getUserName());
						
					}
		});
		
		return affectedRows;
		
	}
	
}
